package oop.db.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import oop.data.Test;
import oop.data.TestVersion;
import oop.data.TestVersionData;
import oop.db.Database;

public class TestVersionDAO {

	public static Random random = new Random(System.currentTimeMillis());

	public static TestVersion fetchById(long id)
			throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ver_id, ver_code, ver_test, ver_data "
					+ "FROM tblTestVersion "
					+ "WHERE ver_id = ?";
	
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, id);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				return fromRow(rs);
			}
			return null;
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static List<TestVersion> fetchByTest(long testId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ver_id, ver_code, ver_test, ver_data "
					+ "FROM tblTestVersion " 
					+ "WHERE ver_test = ?";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, testId);
			rs = stmt.executeQuery();
			
			List<TestVersion> versions = new ArrayList<TestVersion>();
			while (rs.next()) {
				versions.add(fromRow(rs));
			}
			return versions;
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static TestVersion fetchRandomlyByTest(long testId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ver_id, ver_code, ver_test, ver_data "
					+ "FROM tblTestVersion " 
					+ "WHERE ver_test = ? " 
					+ "ORDER BY RAND() " 
					+ "LIMIT 0, 1";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, testId);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				return fromRow(rs);
			}
			return null;
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static TestVersion fetchByTestUser(long testId, long userId) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT ver_id, ver_code, ver_test, ver_data "
					+ "FROM tblTestVersion " 
					+ "WHERE ver_test = ? AND ver_id NOT IN (" +
							"SELECT hist_version FROM tblHistory " +
							"WHERE hist_user = ?) " 
					+ "ORDER BY RAND() " 
					+ "LIMIT 1";
			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, testId);
			stmt.setLong(2, userId);
			rs = stmt.executeQuery();
			
			if (rs.next()) {
				return fromRow(rs);
			}
			return null;
		} finally {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		}
	}

	public static TestVersion create(String code, Test test) throws SQLException {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			String sql = "INSERT INTO `tblTestVersion` (ver_test, ver_code, ver_data) "
					+ "VALUES (?, ?, ?)";

			conn = Database.get().getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setLong(1, test.getId());
			stmt.setString(2, code);
			TestVersionData data = TestVersionData.fromTest(test);
			stmt.setString(3, data.toJson());
			stmt.executeUpdate();

			rs = stmt.getGeneratedKeys();
			rs.next();
			long id = rs.getLong(1);
			
			return new TestVersion(id, code, test.getId(), data);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
			if (rs != null) rs.close();
		}
	}

	private static TestVersion fromRow(ResultSet rs) throws SQLException {
		return new TestVersion(rs.getLong(1), rs.getString(2), rs
				.getLong(3), TestVersionData.fromJson(rs.getString(4)));
	}
	
	public static int drop(long id) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = "DELETE FROM tblTestVersion WHERE ver_id = " + id;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}
	
	/**
	 * Thử xoá các phiên bản đề thi của một đề thi cho trước. Nếu không thể
	 * xoá phiên bản (do đã được dùng để chấm bài hoặc thi trực tuyến) thì
	 * sẽ ném ra ngoại lệ. 
	 * @param testId - Mã số đề thi.
	 * @throws SQLException
	 */
	public static void tryDropByTest(long testId) throws SQLException {
		Connection conn = null;
		CallableStatement stmt = null;
		try {
			conn = Database.get().getConnection();
			stmt = conn.prepareCall("{call spTryDeleteTestVersion(?)}");
			stmt.setLong(1, testId);
			stmt.execute();
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

	public static int dropByTest(long testId) throws SQLException {
		Connection conn = null;
		Statement stmt = null;
		try {
			String sql = "DELETE FROM tblTestVersion WHERE ver_test = " + testId;
			conn = Database.get().getConnection();
			stmt = conn.createStatement();
			return stmt.executeUpdate(sql);
		} finally {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		}
	}

}
